using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.ComponentModel;

namespace SqlScriptCreator.Generators
{
    public class CaseBlockGenerator : IScriptGenerator
    {
        private string _whenColumnName = "";

        [CategoryAttribute("Source table settings"),
                 DescriptionAttribute("Name of the column to use for the WHEN values in the CASE statement.")]
        public string WhenColumnName
        {
            get { return _whenColumnName; }
            set { _whenColumnName = value; }
        }

        private string _thenColumnName = "";

        [CategoryAttribute("Source table settings"),
                DescriptionAttribute("The name of the column to use for the THEN values in the CASE statement.")]
        public string ThenColumnName
        {
            get { return _thenColumnName; }
            set { _thenColumnName = value; }
        }

        private string _thenValue = "1";

        [CategoryAttribute("Source table settings"),
                 DescriptionAttribute("The dafault value to use for the then values (when ThenColumn is empty).")]
        public string ThenValue
        {
            get { return _thenValue; }
            set { _thenValue = value; }
        }
        private string _commentColumn = "";

        [CategoryAttribute("Source table settings"),
                 DescriptionAttribute("Column that is used to generate comments.")]
        public string CommentColumn
        {
            get { return _commentColumn; }
            set { _commentColumn = value; }
        }
	


        #region IScriptGenerator Members

        override public string ToString()
        {
            return "Case-block for column results";
        }

        public string generateScript(String query, DataTable dtQueryResult, DataTable dtParameters)
        {
            StringBuilder sb = new StringBuilder();
            List<string> whenValues = new List<string>();
            List<string> thenValues = new List<string>();
            List<string> comments = new List<string>();
            string quote = "";
            string thencontent;

            if (dtQueryResult != null)
            {
                DataTable dtData = dtQueryResult;
                if (WhenColumnName != null && dtData.Columns.Contains(WhenColumnName))
                {
                    int indexOfThenColumn = 0;
                    int indexOfWhenColumn = dtData.Columns.IndexOf(WhenColumnName);
                    int indexOfCommentColumn = 0;
                    string whenDataType = dtParameters.Rows[indexOfWhenColumn]["DataTypeName"].ToString();
                    string thenDataType = "";
                    string commentDataType = "";

                    if (ThenColumnName != null && ThenColumnName.Length > 0) {
                        indexOfThenColumn = dtData.Columns.IndexOf(ThenColumnName);
                        thenDataType = dtParameters.Rows[indexOfThenColumn]["DataTypeName"].ToString();
                    }

                    if (CommentColumn != null && CommentColumn.Length > 0)
                    {
                        indexOfCommentColumn = dtData.Columns.IndexOf(CommentColumn);
                        commentDataType = dtParameters.Rows[indexOfCommentColumn]["DataTypeName"].ToString();
                    }

                    foreach (DataRow row in dtData.Rows)
                    {
                        if (!whenValues.Contains(row[WhenColumnName].ToString()))
                        {
                            whenValues.Add(SqlTools.getSqlDataRepresentation(row[WhenColumnName], whenDataType));

                            if (ThenColumnName != null && ThenColumnName.Length > 0)
                                thenValues.Add(SqlTools.getSqlDataRepresentation(row[ThenColumnName], thenDataType));
                            else
                                thenValues.Add(ThenValue);

                            if (CommentColumn != null && dtData.Columns.Contains(CommentColumn)) {
                                comments.Add(" -- " + row[CommentColumn].ToString());
                            }
                            else comments.Add("");
                        }

                    }

                    sb.AppendLine("CASE " + WhenColumnName);
                    for (int i=0; i < whenValues.Count; i++)
                    {
                        sb.Append("\tWHEN " + whenValues[i] + 
                            " THEN "+thenValues[i]);
                        sb.AppendLine(comments[i]);
                    }
                    sb.AppendLine("\tELSE 0");
                    sb.AppendLine("END");
                }
            }
            return sb.ToString();
        }

        #endregion
    }
}
